const mysql = require("mysql2")


const pool = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "123456", // 改成你自己的MySQL密码 并给自己的数据库添加school
    database: "school",
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
})

// 测试数据库连接
pool.getConnection((err, connection) => {
    if (err) {
        console.error('数据库连接失败:', err.message)
    } else {
        console.log('数据库连接成功!')
        connection.release()
    }
})

const promisePool = pool.promise()


class StudentDB {

    static async getAllStudents() {
        try {
            // 先测试数据库和表是否存在
            await promisePool.execute("SELECT 1 FROM students LIMIT 1")
            const [rows] = await promisePool.execute("SELECT * FROM students ORDER BY id DESC")
            return rows
        } catch (error) {
            console.error("获取学生列表失败:", error)
            console.error("错误代码:", error.code)
            console.error("错误消息:", error.message)
            if (error.code === 'ER_NO_SUCH_TABLE') {
                console.error("错误原因: students表不存在，请运行scripts/database-setup.sql建表脚本")
            }
            throw error
        }
    }


    static async getStudentById(id) {
        try {
            const [rows] = await promisePool.execute("SELECT * FROM students WHERE id = ?", [id])
            return rows[0]
        } catch (error) {
            console.error("获取学生信息失败:", error)
            throw error
        }
    }


    static async addStudent(studentData) {
        const { name, age, gender, major, score } = studentData
        try {
            const [result] = await promisePool.execute(
                "INSERT INTO students (name, age, gender, major, score) VALUES (?, ?, ?, ?, ?)", [name, age, gender, major, score],
            )
            return result
        } catch (error) {
            console.error("添加学生失败:", error)
            throw error
        }
    }

    static async updateStudent(id, studentData) {
        const { name, age, gender, major, score } = studentData
        try {
            const [result] = await promisePool.execute(
                "UPDATE students SET name = ?, age = ?, gender = ?, major = ?, score = ? WHERE id = ?", [name, age, gender, major, score, id],
            )
            return result
        } catch (error) {
            console.error("更新学生信息失败:", error)
            throw error
        }
    }


    static async deleteStudent(id) {
        try {
            const [result] = await promisePool.execute("DELETE FROM students WHERE id = ?", [id])
            return result
        } catch (error) {
            console.error("删除学生失败:", error)
            throw error
        }
    }

    // 分页和搜索功能
    static async getStudentsWithPagination({ search, gender, major, offset, limit }) {
        try {
            let whereClause = "WHERE 1=1"
            let params = []

            // 构建搜索条件
            if (search && search.trim()) {
                whereClause += " AND (name LIKE ? OR major LIKE ?)"
                params.push(`%${search.trim()}%`, `%${search.trim()}%`)
            }

            if (gender) {
                whereClause += " AND gender = ?"
                params.push(gender)
            }

            if (major) {
                whereClause += " AND major = ?"
                params.push(major)
            }

            // 确保 limit 和 offset 是整数
            const limitInt = parseInt(limit) || 10
            const offsetInt = parseInt(offset) || 0

            // 获取总数
            const [countResult] = await promisePool.execute(
                `SELECT COUNT(*) as total FROM students ${whereClause}`,
                params
            )
            const total = countResult[0].total

            // 获取分页数据
            const [students] = await promisePool.execute(
                `SELECT * FROM students ${whereClause} ORDER BY id DESC LIMIT ? OFFSET ?`, [...params, limitInt, offsetInt]
            )

            return { students, total }
        } catch (error) {
            console.error("分页获取学生列表失败:", error)
            throw error
        }
    }

    // 获取所有专业（用于筛选下拉框）
    static async getAllMajors() {
        try {
            const [rows] = await promisePool.execute(
                "SELECT DISTINCT major FROM students ORDER BY major"
            )
            return rows.map(row => row.major)
        } catch (error) {
            console.error("获取专业列表失败:", error)
            throw error
        }
    }

    // 获取统计数据
    static async getStatistics() {
        try {
            // 总学生数
            const [totalResult] = await promisePool.execute("SELECT COUNT(*) as total FROM students")
            const total = totalResult[0].total

            // 性别分布
            const [genderStats] = await promisePool.execute(`
        SELECT gender, COUNT(*) as count 
        FROM students 
        GROUP BY gender
      `)

            // 专业分布
            const [majorStats] = await promisePool.execute(`
        SELECT major, COUNT(*) as count 
        FROM students 
        GROUP BY major 
        ORDER BY count DESC
      `)

            // 成绩统计
            const [scoreStats] = await promisePool.execute(`
        SELECT 
          AVG(score) as avgScore,
          MAX(score) as maxScore,
          MIN(score) as minScore,
          COUNT(CASE WHEN score >= 90 THEN 1 END) as excellent,
          COUNT(CASE WHEN score >= 80 AND score < 90 THEN 1 END) as good,
          COUNT(CASE WHEN score >= 70 AND score < 80 THEN 1 END) as average,
          COUNT(CASE WHEN score >= 60 AND score < 70 THEN 1 END) as pass,
          COUNT(CASE WHEN score < 60 THEN 1 END) as fail
        FROM students
      `)

            // 年龄分布
            const [ageStats] = await promisePool.execute(`
        SELECT 
          age,
          COUNT(*) as count
        FROM students 
        GROUP BY age 
        ORDER BY age
      `)

            return {
                total,
                genderStats,
                majorStats,
                scoreStats: scoreStats[0],
                ageStats
            }
        } catch (error) {
            console.error("获取统计数据失败:", error)
            throw error
        }
    }

    // 批量删除学生
    static async batchDeleteStudents(studentIds) {
        try {
            const placeholders = studentIds.map(() => '?').join(',')
            const [result] = await promisePool.execute(
                `DELETE FROM students WHERE id IN (${placeholders})`,
                studentIds
            )
            return result
        } catch (error) {
            console.error("批量删除学生失败:", error)
            throw error
        }
    }
}

module.exports = StudentDB